use cross_rs::IResult;
use cross_rs::fasttime::b2_time::TimeFT;
use rusqlite::Connection;


/// Sqlite数据文件封装类
pub struct SqliteDb {
    pub file_数据库文件: String,
    pub name_工程名称: String,
    pub list_已创建的表名: Vec<String>,

    conn: Connection,
}

impl SqliteDb {
    pub const COUNT_PER_PAGE: usize = 1800;
    pub fn new(file_数据库文件: String, name_工程名称: String) -> Self {
        let conn = match rusqlite::Connection::open(&file_数据库文件) {
            Ok(v) => v,
            Err(e) => {
                let msg = format!("SqliteDb::new: 创建Sqlite数据库 {file_数据库文件} 失败！{e:?}");
                panic!("{msg}");
            },
        };

        Self {
            file_数据库文件,
            name_工程名称,
            list_已创建的表名: vec![],
            conn,
        }
    }

    
    pub fn crate_创建多个表格(&mut self, list_表名_sql建表: &Vec<(String, String)>) -> IResult<usize> {
        let mut count_需要新建表数量 = 0;
        for (表名, _) in list_表名_sql建表  {
            if !self.list_已创建的表名.contains(表名) {
                count_需要新建表数量 += 1;
            }
        }
        if count_需要新建表数量 == 0 {
            return Ok(count_需要新建表数量);
        }
        
        let tx = self.conn.transaction()?;
        for (表名, sql_建表) in list_表名_sql建表 {
            if self.list_已创建的表名.contains(表名) {
                continue;
            }
            tx.execute(sql_建表, ())?;
        }
        tx.commit()?;
        self.flush();
        
        if list_表名_sql建表.len() > 0{
            info!("SqliteDb::crate_创建多个表格: 已创建 {count_需要新建表数量} 个表格 / 输入 {} 条建表语句.", list_表名_sql建表.len());
        }

        Ok(count_需要新建表数量)
    }

    pub fn insert_插入许多(&mut self, list_sql_插入: &Vec<String>) -> IResult<usize> {
        let len = list_sql_插入.len();
        if len == 0 {
            return Ok(len);
        }
        let tx = self.conn.transaction()?;

        for sql in list_sql_插入  {
            tx.execute(sql, ())?;
        }
        tx.commit()?;
        
        Ok(len)
    }

    pub fn insert_插入许多_含blob(&mut self, list_sql_插入: Vec<(String, Option<&Vec<u8>>)>) -> IResult<usize> {
        /*
        let sql = format!(
            "INSERT INTO {TABLE_NAME}
 (年月日, 文件夹, 文件名, 创建时间, 修改时间, 文件大小_raw, 文件大小_xz, 分块数量_xz, 本块索引_xz, 本块大小_xz, blob_xz)
 VALUES ({年月日}, '{文件夹}', '{文件名}', {创建时间}, {修改时间}, {文件大小_raw}, {文件大小_xz}, {分块数量_xz}, {本块索引_xz}, {本块大小_xz}, ?);"
        );
         */
        let len = list_sql_插入.len();
        if len == 0 {
            return Ok(len);
        }
        // let mut conn = self.conn.lock().expect("sqlite_db::insert_插入许多: unlock conn error");
        let tx = self.conn.transaction()?;

        for (sql, blob) in list_sql_插入.iter() {
            tx.execute(sql, (blob,))?;
        }
        tx.commit()?;
        
        Ok(len)
    }
    
    pub fn reset_重置(&mut self, file_数据库文件: String, name_工程名称: String) {
        self.flush();

        let conn = match rusqlite::Connection::open(&file_数据库文件) {
            Ok(v) => v,
            Err(e) => {
                let msg = format!("SqliteDb::reset_重置: 创建Sqlite数据库 {file_数据库文件} 失败！{e:?}");
                panic!("{msg}");
            },
        };
        self.file_数据库文件 = file_数据库文件;
        self.name_工程名称 = name_工程名称;
        self.list_已创建的表名 = vec![];
        self.conn = conn;
    }

    pub fn flush(&mut self) {
        match self.conn.cache_flush() {
            Ok(_) => (),
            Err(e) => {
                let msg = format!("SqliteDb::flush: 刷新Sqlite原数据库 {} 失败！{e:?}", self.file_数据库文件);
                panic!("{msg}");
            },
        }
    }

    pub fn query_ids(&mut self, sql: &str) -> IResult<Vec<i64>> {
        let mut stmt = self.conn.prepare(sql)?;
        let mut rows = stmt.query([])?;
        let mut list_id = vec![];
        while let Some(row) = rows.next()? {
            let id: i64 = row.get(0)?;
            list_id.push(id);
        }

        Ok(list_id)
    }


}


// /// Sqlite数据文件封装类
// /// 从上面抄下来的, 不同的是插入支持Blob
// pub struct SqliteDbBlob {
//     pub file_数据库文件: String,
//     pub name_工程名称: String,
//     pub list_sql_建表: Vec<String>,

//     conn: Mutex<Connection>,
//     list_sql_插入: SegQueue<(String, Option<Vec<u8>>)>,
//     time_上次写入时间: AtomicCell<TimeFT>,
// }

// impl SqliteDbBlob {
//     pub fn new(file_数据库文件: String, name_工程名称: String, list_sql_建表: Vec<String>,) -> Self {
//         let conn = match conn_创建连接(&file_数据库文件, &list_sql_建表) {
//             Ok(v) => v,
//             Err(e) => {
//                 let msg = format!("SqliteDb::new: 创建Sqlite数据库 {file_数据库文件} 失败！{e:?}");
//                 panic!("{msg}");
//             },
//         };

//         Self {
//             file_数据库文件,
//             name_工程名称,
//             list_sql_建表,
//             conn: Mutex::new(conn),
//             list_sql_插入: SegQueue::new(),
//             time_上次写入时间: AtomicCell::new(TimeFT::now()),
//         }
//     }
//     pub fn close(&self) {
//         self.flush().expect("SqliteDbBlob::close: error.");
//         // match self.conn.close() {
//         //     Ok(_) => println!("SqliteDb::close: Sqlite数据库 {} 关闭成功！", self.file_数据库文件),
//         //     Err((_, e)) => panic!("SqliteDb::close: Sqlite数据库 {} 关闭失败！{e:?}", self.file_数据库文件),
//         // }
//     }

//     pub fn insert(&self, sql_插入: String, data: Option<Vec<u8>>) -> bool {
//         self.list_sql_插入.push((sql_插入, data));
        

//         let is_是否写入 = self.list_sql_插入.len() >= SZ_1800 || TimeFT::now().millis_from_i32(&self.time_上次写入时间.load()) > 10_000;
//         if is_是否写入 {
//             match self.flush() {
//                 Ok(v) => {
//                     // println!("SqliteDb::insert: {} 自动写入 {v} 条数据.", self.name_工程名称);
//                 },
//                 Err(e) => panic!("SqliteDb::insert: {} 自动写入失败！{e:?}.", self.name_工程名称),
//             }
//         };
//         is_是否写入
//     }

//     pub fn flush(&self) -> IResult<usize> {
//         self.time_上次写入时间.store(TimeFT::now());
//         let len = self.list_sql_插入.len();
//         if len == 0 {
//             return Ok(len);
//         }
        
//         let mut conn = self.conn.lock().expect("SqliteDbBlob::flush: unlock conn error.");
//         let tx = conn.transaction()?;
//         loop {
//             match self.list_sql_插入.pop() {
//                 Some((sql, data)) => {
//                     // self.conn.execute(sql.as_str(), (data));
//                     tx.execute(sql.as_str(), (data,))?;
//                 },
//                 None => break,
//             };
//         }
//         tx.commit()?;
        
//         Ok(len)
//     }

// }














const _2000: i32 = 2000;

pub fn save(file_sqlite: &str, list_sql_createtable: &Vec<String>, list_sql_insert: &Vec<String>) -> IResult<i32> {
    // dir_sqlite_逐笔单: &str = "y:/data/stock/tick_sqlite";
    // dir_sqlite_逐笔单: &str = "/var/hzs/data/stock/tick_sqlite";
    if list_sql_createtable.len() < 1 || list_sql_insert.len() < 1 {
        return Ok(-1);
    }
    // match std::fs::remove_file(file_sqlite) {
    //     Ok(_) => (),
    //     Err(e) => println!("sqlite_db::save {file_sqlite} 文件删除失败: {:?}", e),
    // }

    let start = TimeFT::now();
    
    let mut conn = rusqlite::Connection::open(&file_sqlite)?;
    // 2. 建表
    let tx = conn.transaction()?;
    for sql in list_sql_createtable {
        tx.execute(sql, ())?;
    }
    tx.commit()?;




    // 2. 逐条写入
    let count_all = list_sql_insert.len() as i32;
    let n_page = count_all / _2000;
    let remain = count_all % _2000;
    
    let mut idx = 0;
    for _ in 0..n_page {
        // println!("\t{} / {n_page}: {_2000} 条, {}", i_page + 1, TimeFT::now());
        let tx = conn.transaction()?;
        for _ in 0.._2000 {
            let sql = &list_sql_insert[idx]; idx += 1;
            tx.execute(sql, ())?;
        }
        tx.commit()?;

    }
    if remain > 0 {
        let tx = conn.transaction()?;
        for _ in 0..remain {
            let sql = &list_sql_insert[idx]; idx += 1;
            tx.execute(sql, ())?;
        }
        tx.commit()?;
    }
    
    _ = conn.close();
    let mill_ses = TimeFT::now().millis_from_i32(&start);



    Ok(mill_ses)
}

pub fn execute_many(file_sqlite: &str, list_sql: &Vec<String>) -> IResult<i32> {
    if list_sql.len() < 1 {
        return Ok(-1);
    }

    let start = TimeFT::now();
    
    let mut conn = rusqlite::Connection::open(&file_sqlite)?;

    // 2. 逐条写入
    let count_all = list_sql.len() as i32;
    let n_page = count_all / _2000;
    let remain = count_all % _2000;
    
    let mut idx = 0;
    for _ in 0..n_page {
        // println!("\t{} / {n_page}: {_2000} 条, {}", i_page + 1, TimeFT::now());
        let tx = conn.transaction()?;
        for _ in 0.._2000 {
            let sql = &list_sql[idx]; idx += 1;
            tx.execute(sql, ())?;
        }
        tx.commit()?;

    }
    if remain > 0 {
        let tx = conn.transaction()?;
        for _ in 0..remain {
            let sql = &list_sql[idx]; idx += 1;
            tx.execute(sql, ())?;
        }
        tx.commit()?;
    }
    
    _ = conn.close();
    let mill_ses = TimeFT::now().millis_from_i32(&start);



    Ok(mill_ses)
}

pub fn execute_with_blob(file_sqlite: &str, sql: &str, blob: &Vec<u8>) -> IResult {
    let conn = rusqlite::Connection::open(&file_sqlite)?;
    conn.execute(sql, (blob,))?;
    match conn.close() {
        Ok(_) => {
            Ok(())
        },
        Err((_, e)) => {
            Err(format!("{e:?}").as_str())?
        },
    }

}